Brief Full Advanced Search Search Tips
|  |  | To access the contents, click the chapter and section titles. Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6! (Publisher: The Coriolis Group) Author(s): Peter G. Aitken ISBN: 1576102815 Publication Date: 08/01/98 Search this book:
Previous | Table of Contents | Next
First, put the global variable declaration in the General section of the Invoice form’s code:
    Dim InvoiceNumber
Next, add the CreateNewInvoice procedure, as shown in Listing 23.7. Here is a description of how the code works: 1. Create new Connection and Recordset objects. 2. Set up error handling. Error-handling code does nothing more than display error information. 3. Build the connection string for the Connection object. If you are unsure of the details of what goes in this string, see the tip “Connection String Blues.” TIP: Connection String Blues Until you become familiar with connection string syntax, constructing a valid connection string can be a daunting prospect. Fortunately, Visual Basic can help you construct a valid connection string to use with a Connection object. Place a temporary ADO Data control on a form and double-click on its Connection String property in the Properties window to open the object’s property page. Select the Use Connection String option and click on the Build button to display the Data Link Properties dialog box. Make entries here to specify the details of the data connection that you want to establish, then close both the Data Link and Property Page dialog boxes. The ADO Data control will now have the desired connection string in its ConnectionString property. Copy it from there to your code, then delete the ADO Data control. 4. Set other Connection object properties as needed, and then call the Open method to set up the connection to the database. Then, if you are creating a new record (Delete = 0): 5. Open the Recordset, associating it with the previously opened Connection object and populating it with all records from the Invoices table. 6. Execute the Recordset object’s AddNew method, to create a new record. 7. Place new data in the record’s fields. Note that the ! operator is used to indicate a specific record in a specific Recordset. 8. Retrieve the new record’s automatically generated InvNo value and store it in the global variable InvoiceNumber. Or, if you are deleting a record (Delete > 0): 5. Set up a command string to delete records where the InvNo field has the value specified in the Delete argument. 6. Execute the Recordset object’s Open method with this command, to perform the deletion. 7. Set the global variable InvoiceNumber to zero. Whether deleting or adding a record, the final step is to set both the Connection and Recordset object variables to Nothing, because you are done using them. This is automatically done at the end of the procedure when the variables go out of scope, but explicitly destroying them in code is good practice, to ensure that unneeded instances are not left. Listing 23.7 The CreateNewInvoice procedure.
    Public Sub CreateNewInvoice(Delete As Variant)

    ‘ If Delete = 0 then creates a new record
    ‘ in the Invoices table.
    ‘ If Delete > 0 then deletes the record in
    ‘ the Invoices table where InvNo = Delete.

    Dim strConnect As String, cmd As String
    Dim cnInv As New ADODB.Connection
    Dim rsInv As New ADODB.Recordset

    On Error GoTo ADOError

    strConnect = “Provider=Microsoft.Jet.OLEDB.3.51;”
    strConnect = strConnect & “Persist Security Info=False;”
    ‘ Be sure to edit the next line to point
    ‘ at your database file.
    strConnect = strConnect & _
        “Data Source=C:\documents\vb6pe\projects\grapevine\Grapevine.mdb”

    cnInv.ConnectionString = strConnect
    cnInv.ConnectionTimeout = 10
    cnInv.CursorLocation = adUseNone
    cnInv.Open
    If Delete = 0 Then
        rsInv.Open “select * from invoices”, _
            cnInv, adOpenDynamic, adLockOptimistic, adCmdText
        rsInv.AddNew
        rsInv!CustPO = txtCustPO.Text
        rsInv!Date = txtDate.Text
        rsInv!CustID = DataListCustomers.BoundText
        rsInv.Update
        ‘ Store invoice number.
        InvoiceNumber = rsInv!InvNo
    Else
        ‘ Delete the newly added record.
        cmd = “delete * from invoices where InvNo = ” & Delete
        rsInv.Open cmd, cnInv, adOpenDynamic, adLockOptimistic, adCmdText
        InvoiceNumber = 0
    End If

    Set rsInv = Nothing
    Set cnInv = Nothing

    Exit Sub

    ADOError:

    MsgBox (“Create invoice: ” & Err.Description)
    InvoiceNumber = 0

    End Sub
#### Completing The Invoices Form Now that the Select Wine form is complete, and you have written the code to connect to the Invoices table, you can place the finishing touches on the Invoices form. First, briefly review the procedures that are involved in creating a new invoice: 1. User clicks on the Orders button on the main form’s Toolbar to display the Invoices form. 2. User selects a customer and enters the customer PO number. 3. User clicks on the Add Wine button. If necessary, a new record is added to the Invoices table. The Select Wine form is displayed. 4. User selects a wine and enters a quantity, and then clicks on OK. Information about the selected wine is copied back to the Invoices form, and the item is displayed in the DataGrid control. The new record is added to the Items table. If the user cancels the item, the update of the Items table is aborted. The Select Wines form is hidden. 5. Repeat Steps 3 and 4 to add additional wines to the invoice. 6. If the user clicks Save on the Invoices form, the form is hidden (all new data has already been saved). If the user cancels the entire invoice, the new record in the Invoices table is deleted. All associated records in the Items table should be deleted as well. You have already written the code to perform most of these tasks, but you still need to write the code for the Command Buttons on the Invoices form. It is this code that ties everything together. The Click event procedure is presented in Listing 23.8.
Previous | Table of Contents | Next